import pandas as pd
import pymysql

conn = pymysql.connect(
  host="114.116.236.2",
  user="root",
  password="TP7Cf8WrUpuTDGCKtr6&",
  port=3306,
  db="movie_index",
  charset='utf8'
)

# 每次10万条
chunksize = 100000
with pd.read_csv("./ratings.csv",
                 chunksize=chunksize) as reader:
  # 分片插入数据
  for df in reader:
    datas = []
    print("process:", len(df))
    for idx, row in df.iterrows():
      data = (row["UserID"], row["MovieID"],
              row["Rating"], row["Timestamp"])
      datas.append(data)

    sql = """
      insert into movie_rating
      (user_id, movie_id, rating, timestamp) 
      values (%s, %s, %s, %s)"""
    cursor = conn.cursor()
    cursor.executemany(sql, datas)
    conn.commit()

conn.close()
print("success")
